{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1754884 entries, 0 to 1754883\n",
      "Data columns (total 7 columns):\n",
      "user_id          int64\n",
      "merchant_id      int64\n",
      "coupon_id        object\n",
      "discount_rate    object\n",
      "distance         object\n",
      "date_received    object\n",
      "date             object\n",
      "dtypes: int64(2), object(5)\n",
      "memory usage: 93.7+ MB\n",
      "None\n",
      "   user_id  merchant_id coupon_id discount_rate distance date_received  \\\n",
      "0  1439408         2632      null          null        0          null   \n",
      "1  1439408         4663     11002        150:20        1      20160528   \n",
      "2  1439408         2632      8591          20:1        0      20160217   \n",
      "3  1439408         2632      1078          20:1        0      20160319   \n",
      "4  1439408         2632      8591          20:1        0      20160613   \n",
      "\n",
      "       date  \n",
      "0  20160217  \n",
      "1      null  \n",
      "2      null  \n",
      "3      null  \n",
      "4      null  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import date\n",
    "import datetime as dt\n",
    "import os\n",
    "#如果没有keep_default_na=False，加载后空值处就是NAN，且类似coupon_id等处的类型都是float\n",
    "#判断是否是NAN的话是：off_train.date!=off_train.date结果是True即为NAN，否则是非空值\n",
    "#这里使用了keep_default_na=False，使coupon_id等字段的数据类型转化为object可以简单看作是字符串，空值变为null\n",
    "#这时候判断是否是空值便可用off_train.date=='null'\n",
    "\n",
    "#源数据路径\n",
    "DataPath = 'D:/MachineLearning/ofo/ofoOptimization'\n",
    "#预处理后数据存放路径\n",
    "FeaturePath = 'D:/MachineLearning/ofo/ofoOptimization'\n",
    "\n",
    "\n",
    "\n",
    "off_train = pd.read_csv(os.path.join(DataPath,'ccf_offline_stage1_train.csv'),header=0,keep_default_na=False)\n",
    "off_train.columns=['user_id','merchant_id','coupon_id','discount_rate','distance','date_received','date']\n",
    "\n",
    "off_test = pd.read_csv(os.path.join(DataPath,'ccf_offline_stage1_test_revised.csv'),header=0,keep_default_na=False)\n",
    "off_test.columns = ['user_id','merchant_id','coupon_id','discount_rate','distance','date_received']\n",
    "\n",
    "print(off_train.info())\n",
    "print(off_train.head(5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#交叉训练集一：收到券的日期大于4月14日和小于5月14日\n",
    "dataset1 = off_train[(off_train.date_received>='201604014')&(off_train.date_received<='20160514')]\n",
    "#交叉训练集一特征：线下数据中领券和用券日期大于1月1日和小于4月13日\n",
    "feature1 = off_train[(off_train.date>='20160101')&(off_train.date<='20160413')|((off_train.date=='null')&(off_train.date_received>='20160101')&(off_train.date_received<='20160413'))]\n",
    "\n",
    "#交叉训练集二：收到券的日期大于5月15日和小于6月15日\n",
    "dataset2 = off_train[(off_train.date_received>='20160515')&(off_train.date_received<='20160615')]\n",
    "#交叉训练集二特征：线下数据中领券和用券日期大于2月1日和小于5月14日\n",
    "feature2 = off_train[(off_train.date>='20160201')&(off_train.date<='20160514')|((off_train.date=='null')&(off_train.date_received>='20160201')&(off_train.date_received<='20160514'))]\n",
    "\n",
    "#测试集\n",
    "dataset3 = off_test\n",
    "#测试集特征 :线下数据中领券和用券日期大于3月15日和小于6月30日的\n",
    "feature3 = off_train[((off_train.date>='20160315')&(off_train.date<='20160630'))|((off_train.date=='null')&(off_train.date_received>='20160315')&(off_train.date_received<='20160630'))]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def is_firstlastone(x):\n",
    "    if x==0:\n",
    "        return 1\n",
    "    elif x>0:\n",
    "        return 0\n",
    "    else:\n",
    "        return -1\n",
    "\n",
    "def get_day_gap_before(s):\n",
    "    date_received,dates = s.split('-')\n",
    "    dates = dates.split(':')\n",
    "    gaps = []\n",
    "    for d in dates:\n",
    "        #将时间差转化为天数\n",
    "        this_gap = (dt.date(int(date_received[0:4]),int(date_received[4:6]),int(date_received[6:8]))-dt.date(int(d[0:4]),int(d[4:6]),int(d[6:8]))).days\n",
    "        if this_gap>0:\n",
    "            gaps.append(this_gap)\n",
    "    if len(gaps)==0:\n",
    "        return -1\n",
    "    else:\n",
    "        return min(gaps)\n",
    "def get_day_gap_after(s):\n",
    "    date_received,dates = s.split('-')\n",
    "    dates = dates.split(':')\n",
    "    gaps = []\n",
    "    for d in dates:\n",
    "        this_gap = (dt.datetime(int(d[0:4]),int(d[4:6]),int(d[6:8]))-dt.datetime(int(date_received[0:4]),int(date_received[4:6]),int(date_received[6:8]))).days\n",
    "        if this_gap>0:\n",
    "            gaps.append(this_gap)\n",
    "    if len(gaps)==0:\n",
    "        return -1\n",
    "    else:\n",
    "        return min(gaps)\n",
    "    \n",
    "def GetOtherFeature(dataset):\n",
    "    t = dataset[['user_id']].copy()\n",
    "    t['this_month_user_receive_all_coupon_count'] = 1\n",
    "    t = t.groupby('user_id').agg('sum').reset_index()\n",
    "    \n",
    "    t1 = dataset[['user_id','coupon_id']].copy()\n",
    "    t1['this_month_user_receive_same_coupn_count'] = 1\n",
    "    t1 = t1.groupby(['user_id','coupon_id']).agg('sum').reset_index()\n",
    "    \n",
    "    t2 = dataset[['user_id','coupon_id','date_received']].copy()\n",
    "    t2.date_received = t2.date_received.astype('str')\n",
    "    #如果出现相同的用户接收相同的优惠券在接收时间上用‘：’连接上第n次接受优惠券的时间\n",
    "    t2 = t2.groupby(['user_id','coupon_id'])['date_received'].agg(lambda x:':'.join(x)).reset_index()\n",
    "    #将接收时间的一组按着':'分开，这样就可以计算接受了优惠券的数量,apply是合并\n",
    "    t2['receive_number'] = t2.date_received.apply(lambda s:len(s.split(':')))\n",
    "    t2 = t2[t2.receive_number > 1]\n",
    "    #最大接受的日期\n",
    "  \n",
    "    t2['max_date_received'] = t2.date_received.apply(lambda s:max([int(d) for d in s.split(':')]))\n",
    "    #最小的接收日期\n",
    "    t2['min_date_received'] = t2.date_received.apply(lambda s:min([int(d) for d in s.split(':')]))\n",
    "    t2 = t2[['user_id','coupon_id','max_date_received','min_date_received']]\n",
    "\n",
    "    t3 = dataset[['user_id','coupon_id','date_received']]\n",
    "    #将两表融合只保留左表数据,这样得到的表，相当于保留了最近接收时间和最远接受时间\n",
    "    t3 = pd.merge(t3,t2,on=['user_id','coupon_id'],how='left')\n",
    "    #这个优惠券最近接受时间\n",
    "    t3['this_month_user_receive_same_coupon_lastone']= t3.max_date_received-t3.date_received.astype(int)\n",
    "    #这个优惠券最远接受时间\n",
    "    t3['this_month_user_receive_same_coupon_firstone'] = t3.date_received.astype(int)-t3.min_date_received\n",
    "    \n",
    "    t3.this_month_user_receive_same_coupon_lastone = t3.this_month_user_receive_same_coupon_lastone.apply(is_firstlastone)\n",
    "    t3.this_month_user_receive_same_coupon_firstone = t3.this_month_user_receive_same_coupon_lastone.apply(is_firstlastone)\n",
    "    t3 = t3[['user_id','coupon_id','date_received','this_month_user_receive_same_coupon_lastone','this_month_user_receive_same_coupon_firstone']]\n",
    "       \n",
    "    #提取第四个特征,一个用户所接收到的所有优惠券的数量\n",
    "    t4 = dataset[['user_id','date_received']].copy()\n",
    "    t4['this_day_receive_all_coupon_count'] = 1\n",
    "    t4 = t4.groupby(['user_id','date_received']).agg('sum').reset_index()\n",
    "\n",
    "    #提取第五个特征,一个用户不同时间所接收到不同优惠券的数量\n",
    "    t5 = dataset[['user_id','coupon_id','date_received']].copy()\n",
    "    t5['this_day_user_receive_same_coupon_count'] = 1\n",
    "    t5 = t5.groupby(['user_id','coupon_id','date_received']).agg('sum').reset_index()\n",
    "    #一个用户不同优惠券 的接受时间\n",
    "    t6 = dataset[['user_id','coupon_id','date_received']].copy()\n",
    "    t6.date_received = t6.date_received.astype('str')\n",
    "    t6 = t6.groupby(['user_id','coupon_id'])['date_received'].agg(lambda x:':'.join(x)).reset_index()\n",
    "    t6.rename(columns={'date_received':'dates'},inplace = True)\n",
    "    \n",
    "    t7 = dataset[['user_id','coupon_id','date_received']]\n",
    "    t7 = pd.merge(t7,t6,on=['user_id','coupon_id'],how='left')\n",
    "    t7['date_received_date'] = t7.date_received.astype('str')+'-'+t7.dates\n",
    "    t7['day_gap_before'] = t7.date_received_date.apply(get_day_gap_before)\n",
    "    t7['day_gap_after'] = t7.date_received_date.apply(get_day_gap_after)\n",
    "    t7 = t7[['user_id','coupon_id','date_received','day_gap_before','day_gap_after']]\n",
    "    \n",
    "    other_feature = pd.merge(t1,t,on='user_id')\n",
    "    other_feature = pd.merge(other_feature,t3,on=['user_id','coupon_id'])\n",
    "    other_feature = pd.merge(other_feature,t4,on=['user_id','date_received'])\n",
    "    other_feature = pd.merge(other_feature,t5,on=['user_id','coupon_id','date_received'])\n",
    "    other_feature = pd.merge(other_feature,t7,on=['user_id','coupon_id','date_received'])\n",
    "    return other_feature\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_discount_rate(s):\n",
    "    s = str(s)\n",
    "    s = s.split(':')\n",
    "    if len(s) == 1:\n",
    "        return float(s[0])\n",
    "    else:\n",
    "        return 1.0-float(s[1])/float(s[0])\n",
    "def get_discount_man(s):\n",
    "    s = str(s)\n",
    "    s = s.split(':')\n",
    "    if len(s)==1:\n",
    "        return 'null'\n",
    "    else:\n",
    "        return int(s[0])\n",
    "def get_discount_jian(s):\n",
    "    s = str(s)\n",
    "    s = s.split(':')\n",
    "    if len(s) == 1:\n",
    "        return 'null'\n",
    "    else:\n",
    "        return int(s[1])\n",
    "\n",
    "def is_man_jian(s):\n",
    "    s = str(s)\n",
    "    s = s.split(':')\n",
    "    if len(s)==1:\n",
    "        return 0\n",
    "    else:\n",
    "        return 1\n",
    "def  GetCouponRelatedFeature(dataset,feature):\n",
    "    #为了求得每个feature中date最大的日期，其会被用在求days_distance字段\n",
    "    t = feature[feature['date']!='null']['date'].unique()\n",
    "    t = max(t)\n",
    " \n",
    "    dataset['day_of_week'] = dataset.date_received.astype('str').apply(lambda x:date(int(x[0:4]),int(x[4:6]),int(x[6:8])).weekday()+1)\n",
    "    #显示时间是几月\n",
    "    dataset['day_of_month'] = dataset.date_received.astype('str').apply(lambda x:int(x[6:8]))\n",
    "    #显示时期和截止日之间的天数\n",
    "    dataset['days_distance'] = dataset.date_received.astype('str').apply(lambda x:(date(int(x[0:4]),int(x[4:6]),int(x[6:8]))-date(int(t[0:4]),int(t[4:6]),int(t[6:8]))).days)\n",
    "    #显示满了多少钱后开始减\n",
    "    dataset['discount_man'] = dataset.discount_rate.apply(get_discount_man)\n",
    "    #显示满减的减少的钱\n",
    "    dataset['discount_jian'] = dataset.discount_rate.apply(get_discount_jian)\n",
    "    #返回优惠券是否是满减券\n",
    "    dataset['is_man_jian'] = dataset.discount_rate.apply(is_man_jian)\n",
    "    #显示打折力度\n",
    "    dataset['discount_rate'] = dataset.discount_rate.apply(calc_discount_rate)\n",
    "    d = dataset[['coupon_id']]\n",
    "    d['coupon_count'] = 1\n",
    "    #显示每一种优惠券的数量\n",
    "    d = d.groupby('coupon_id').agg('sum').reset_index()\n",
    "    dataset = pd.merge(dataset,d,on='coupon_id',how='left')\n",
    "    return dataset\n",
    "    \n",
    "    \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def GetMerchantRelatedFeature(feature):\n",
    "    merchant = feature[['merchant_id','coupon_id','distance','date_received','date']].copy()\n",
    "    t = merchant[['merchant_id']].copy()\n",
    "    #删除重复行数据\n",
    "    t.drop_duplicates(inplace=True)\n",
    "\n",
    "    #卖出的商品\n",
    "    t1 = merchant[merchant.date!='null'][['merchant_id']].copy()\n",
    "    t1['total_sales'] = 1\n",
    "    #每个商品的销售数量\n",
    "    t1 = t1.groupby('merchant_id').agg('sum').reset_index()\n",
    "\n",
    "    #使用了优惠券消费的商品，正样本\n",
    "    t2 = merchant[(merchant.date!='null')&(merchant.coupon_id!='null')][['merchant_id']].copy()\n",
    "    t2['sales_use_coupon'] = 1\n",
    "    t2 = t2.groupby('merchant_id').agg('sum').reset_index()\n",
    "\n",
    "    #商品的优惠券的总数量\n",
    "    t3 = merchant[merchant.coupon_id != 'null'][['merchant_id']].copy()\n",
    "    t3 ['total_coupon'] = 1\n",
    "    t3 = t3.groupby('merchant_id').agg('sum').reset_index()\n",
    "    \n",
    "    #商品销量和距离的关系\n",
    "    t4 = merchant[(merchant.date != 'null')&(merchant.coupon_id != 'null')][['merchant_id','distance']].copy()\n",
    "    #下面三行代码的主要作用就是为了将distance字段的数据类型转化为int\n",
    "    #把数据中的null值全部替换为-1\n",
    "    t4.replace('null',-1,inplace=True)\n",
    "    t4.distance = t4.distance.astype('int')\n",
    "    #再把数据中的-1全部替换为NaN\n",
    "    t4.replace(-1,np.nan,inplace=True)\n",
    "\n",
    "    #返回用户离商品的距离最小值\n",
    "    t5 = t4.groupby('merchant_id').agg('min').reset_index()\n",
    "    t5.rename(columns={'distance':'merchant_min_distance'},inplace = True)\n",
    "\n",
    "    #返回用户离商品的距离最大值\n",
    "    t6 = t4.groupby('merchant_id').agg('max').reset_index()\n",
    "    t6.rename(columns={'distance':'merchant_max_distance'},inplace = True)\n",
    "    #print(t6)\n",
    "\n",
    "    #返回距离的平均值\n",
    "    t7 = t4.groupby('merchant_id').agg('mean').reset_index()\n",
    "    t7.rename(columns = {'distance':'merchant_mean_distance'},inplace= True)\n",
    "    #返回距离的中位值\n",
    "    t8 = t4.groupby('merchant_id').agg('median').reset_index()\n",
    "    t8.rename(columns={'distance':'merchant_median_distance'},inplace = True)\n",
    "    \n",
    "    merchant_feature = pd.merge(t,t1,on='merchant_id',how='left')\n",
    "    merchant_feature = pd.merge(merchant_feature,t2,on='merchant_id',how='left')\n",
    "    merchant_feature = pd.merge(merchant_feature,t3,on='merchant_id',how='left')\n",
    "    merchant_feature = pd.merge(merchant_feature,t5,on='merchant_id',how='left')\n",
    "    merchant_feature = pd.merge(merchant_feature,t6,on='merchant_id',how='left')\n",
    "    merchant_feature = pd.merge(merchant_feature,t7,on='merchant_id',how='left')\n",
    "    merchant_feature = pd.merge(merchant_feature,t8,on='merchant_id',how='left')\n",
    "\n",
    "    #将数据中的NaN用0来替换\n",
    "    merchant_feature.sales_use_coupon = merchant_feature.sales_use_coupon.replace(np.nan,0)\n",
    "    #优惠券的使用率\n",
    "    merchant_feature['merchant_coupon_transfer_rate'] = merchant_feature.sales_use_coupon.astype('float')/merchant_feature.total_coupon\n",
    "    #即卖出商品中使用优惠券的占比\n",
    "    merchant_feature['coupon_rate'] = merchant_feature.sales_use_coupon.astype('float') / merchant_feature.total_sales\n",
    "    #将数据中的NaN用0来替换\n",
    "    merchant_feature.total_coupon = merchant_feature.total_coupon.replace(np.nan,0)\n",
    "\n",
    "    return merchant_feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_user_date_datereceived_gap(s):\n",
    "    s = s.split(':')\n",
    "    return (date(int(s[0][0:4]),int(s[0][4:6]),int(s[0][6:8])) - date(int(s[1][0:4]),int(s[1][4:6]),int(s[1][6:8]))).days\n",
    "def GetUserRelatedFeature(feature):\n",
    "    #for dataset3\n",
    "    user = feature[['user_id','merchant_id','coupon_id','discount_rate','distance','date_received','date']].copy()\n",
    "\n",
    "    t = user[['user_id']].copy()\n",
    "    t.drop_duplicates(inplace=True)\n",
    "     \n",
    "    #客户一共买的商品\n",
    "    t1 = user[user.date!='null'][['user_id','merchant_id']].copy()\n",
    "    t1.drop_duplicates(inplace=True)\n",
    "    t1.merchant_id = 1\n",
    "    t1 = t1.groupby('user_id').agg('sum').reset_index()\n",
    "    t1.rename(columns={'merchant_id':'count_merchant'},inplace=True)\n",
    "    \n",
    "    #客户使用优惠券线下购买距离商店的最小距离\n",
    "    t2 = user[(user.date!='null')&(user.coupon_id!='null')][['user_id','distance']]\n",
    "    t2.replace('null',-1,inplace=True)\n",
    "    t2.distance = t2.distance.astype('int')\n",
    "    t2.replace(-1,np.nan,inplace=True)\n",
    "    t3 = t2.groupby('user_id').agg('min').reset_index()\n",
    "    t3.rename(columns={'distance':'user_min_distance'},inplace=True)\n",
    "    \n",
    "    #客户使用优惠券线下购买距离商店的最大距离\n",
    "    t4 = t2.groupby('user_id').agg('max').reset_index()\n",
    "    t4.rename(columns={'distance':'user_max_distance'},inplace=True)\n",
    "    \n",
    "    #客户使用优惠券线下购买距离商店的平均距离\n",
    "    t5 = t2.groupby('user_id').agg('mean').reset_index()\n",
    "    t5.rename(columns={'distance':'user_mean_distance'},inplace=True)\n",
    "    \n",
    "    #客户使用优惠券线下购买距离商店的中间距离\n",
    "    t6 = t2.groupby('user_id').agg('median').reset_index()\n",
    "    t6.rename(columns={'distance':'user_median_distance'},inplace=True)\n",
    "\n",
    "    #客户使用优惠券购买的次数\n",
    "    t7 = user[(user.date!='null')&(user.coupon_id!='null')][['user_id']]\n",
    "    t7['buy_use_coupon'] = 1\n",
    "    t7 = t7.groupby('user_id').agg('sum').reset_index()\n",
    "    \n",
    "    #客户使购买的总次数\n",
    "    t8 = user[user.date!='null'][['user_id']]\n",
    "    t8['buy_total'] = 1\n",
    "    t8 = t8.groupby('user_id').agg('sum').reset_index()\n",
    "    \n",
    "    #客户收到优惠券的总数\n",
    "    t9 = user[user.coupon_id!='null'][['user_id']]\n",
    "    t9['coupon_received'] = 1\n",
    "    t9 = t9.groupby('user_id').agg('sum').reset_index()\n",
    "    \n",
    "    #客户从收优惠券到消费的时间间隔\n",
    "    t10 = user[(user.date_received!='null')&(user.date!='null')][['user_id','date_received','date']]\n",
    "    t10['user_date_datereceived_gap'] = t10.date + ':' + t10.date_received\n",
    "    t10.user_date_datereceived_gap = t10.user_date_datereceived_gap.apply(get_user_date_datereceived_gap)\n",
    "    t10 = t10[['user_id','user_date_datereceived_gap']]\n",
    "    \n",
    "    #客户从收优惠券到消费的平均时间间隔\n",
    "    t11 = t10.groupby('user_id').agg('mean').reset_index()\n",
    "    t11.rename(columns={'user_date_datereceived_gap':'avg_user_date_datereceived_gap'},inplace=True)\n",
    "    #客户从收优惠券到消费的最小时间间隔\n",
    "    t12 = t10.groupby('user_id').agg('min').reset_index()\n",
    "    t12.rename(columns={'user_date_datereceived_gap':'min_user_date_datereceived_gap'},inplace=True)\n",
    "    #客户从收优惠券到消费的最大时间间隔\n",
    "    t13 = t10.groupby('user_id').agg('max').reset_index()\n",
    "    t13.rename(columns={'user_date_datereceived_gap':'max_user_date_datereceived_gap'},inplace=True)\n",
    "\n",
    "    user_feature = pd.merge(t,t1,on='user_id',how='left')\n",
    "    user_feature = pd.merge(user_feature,t3,on='user_id',how='left')\n",
    "    user_feature = pd.merge(user_feature,t4,on='user_id',how='left')\n",
    "    user_feature = pd.merge(user_feature,t5,on='user_id',how='left')\n",
    "    user_feature = pd.merge(user_feature,t6,on='user_id',how='left')\n",
    "    user_feature = pd.merge(user_feature,t7,on='user_id',how='left')\n",
    "    user_feature = pd.merge(user_feature,t8,on='user_id',how='left')\n",
    "    user_feature = pd.merge(user_feature,t9,on='user_id',how='left')\n",
    "    user_feature = pd.merge(user_feature,t11,on='user_id',how='left')\n",
    "    user_feature = pd.merge(user_feature,t12,on='user_id',how='left')\n",
    "    user_feature = pd.merge(user_feature,t13,on='user_id',how='left')\n",
    "    user_feature.count_merchant = user_feature.count_merchant.replace(np.nan,0)\n",
    "    user_feature.buy_use_coupon = user_feature.buy_use_coupon.replace(np.nan,0)\n",
    "    user_feature['buy_use_coupon_rate'] = user_feature.buy_use_coupon.astype('float') / user_feature.buy_total.astype('float')\n",
    "    user_feature['user_coupon_transfer_rate'] = user_feature.buy_use_coupon.astype('float') / user_feature.coupon_received.astype('float')\n",
    "    user_feature.buy_total = user_feature.buy_total.replace(np.nan,0)\n",
    "    user_feature.coupon_received = user_feature.coupon_received.replace(np.nan,0)\n",
    "    return user_feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def GetUserAndMerchantRelatedFeature(feature):\n",
    "    all_user_merchant = feature[['user_id','merchant_id']].copy()\n",
    "    all_user_merchant.drop_duplicates(inplace=True)\n",
    "\n",
    "    #一个客户在一个商家一共买的次数\n",
    "    t = feature[['user_id','merchant_id','date']].copy()\n",
    "    t = t[t.date!='null'][['user_id','merchant_id']]\n",
    "    t['user_merchant_buy_total'] = 1\n",
    "    t = t.groupby(['user_id','merchant_id']).agg('sum').reset_index()\n",
    "    t.drop_duplicates(inplace=True)\n",
    "    \n",
    "    #一个客户在一个商家一共收到的优惠券\n",
    "    t1 = feature[['user_id','merchant_id','coupon_id']]\n",
    "    t1 = t1[t1.coupon_id!='null'][['user_id','merchant_id']]\n",
    "    t1['user_merchant_received'] = 1\n",
    "    t1 = t1.groupby(['user_id','merchant_id']).agg('sum').reset_index()\n",
    "    t1.drop_duplicates(inplace=True)\n",
    "    \n",
    "    #一个客户在一个商家使用优惠券购买的次数\n",
    "    t2 = feature[['user_id','merchant_id','date','date_received']]\n",
    "    t2 = t2[(t2.date!='null')&(t2.date_received!='null')][['user_id','merchant_id']]\n",
    "    t2['user_merchant_buy_use_coupon'] = 1\n",
    "    t2 = t2.groupby(['user_id','merchant_id']).agg('sum').reset_index()\n",
    "    t2.drop_duplicates(inplace=True)\n",
    "    \n",
    "    #一个客户在一个商家浏览的次数\n",
    "    t3 = feature[['user_id','merchant_id']]\n",
    "    t3['user_merchant_any'] = 1\n",
    "    t3 = t3.groupby(['user_id','merchant_id']).agg('sum').reset_index()\n",
    "    t3.drop_duplicates(inplace=True)\n",
    "    \n",
    "    #一个客户在一个商家没有使用优惠券购买的次数\n",
    "    t4 = feature[['user_id','merchant_id','date','coupon_id']]\n",
    "    t4 = t4[(t4.date!='null')&(t4.coupon_id=='null')][['user_id','merchant_id']]\n",
    "    t4['user_merchant_buy_common'] = 1\n",
    "    t4 = t4.groupby(['user_id','merchant_id']).agg('sum').reset_index()\n",
    "    t4.drop_duplicates(inplace=True)\n",
    "    \n",
    "    user_merchant = pd.merge(all_user_merchant,t,on=['user_id','merchant_id'],how='left')\n",
    "    user_merchant = pd.merge(user_merchant,t1,on=['user_id','merchant_id'],how='left')\n",
    "    user_merchant = pd.merge(user_merchant,t2,on=['user_id','merchant_id'],how='left')\n",
    "    user_merchant = pd.merge(user_merchant,t3,on=['user_id','merchant_id'],how='left')\n",
    "    user_merchant = pd.merge(user_merchant,t4,on=['user_id','merchant_id'],how='left')\n",
    "    user_merchant.user_merchant_buy_use_coupon = user_merchant.user_merchant_buy_use_coupon.replace(np.nan,0)\n",
    "    user_merchant.user_merchant_buy_common = user_merchant.user_merchant_buy_common.replace(np.nan,0)\n",
    "    user_merchant['user_merchant_coupon_transfer_rate'] = user_merchant.user_merchant_buy_use_coupon.astype('float') / user_merchant.user_merchant_received.astype('float')\n",
    "    user_merchant['user_merchant_coupon_buy_rate'] = user_merchant.user_merchant_buy_use_coupon.astype('float') / user_merchant.user_merchant_buy_total.astype('float')\n",
    "    user_merchant['user_merchant_rate'] = user_merchant.user_merchant_buy_total.astype('float') / user_merchant.user_merchant_any.astype('float')\n",
    "    user_merchant['user_merchant_common_buy_rate'] = user_merchant.user_merchant_buy_common.astype('float') / user_merchant.user_merchant_buy_total.astype('float')\n",
    "    return user_merchant\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_label(s):\n",
    "    s = s.split(':')\n",
    "    if s[0]=='null':\n",
    "        return 0\n",
    "    elif (date(int(s[0][0:4]),int(s[0][4:6]),int(s[0][6:8]))-date(int(s[1][0:4]),int(s[1][4:6]),int(s[1][6:8]))).days<=15:\n",
    "        return 1\n",
    "    else:\n",
    "        return -1\n",
    "    \n",
    "def DataProcess(dataset,feature,TrainFlag):\n",
    "    \n",
    "    other_feature = GetOtherFeature(dataset)\n",
    "    merchant = GetMerchantRelatedFeature(feature)\n",
    "    user = GetUserRelatedFeature(feature)\n",
    "    user_merchant = GetUserAndMerchantRelatedFeature(feature)\n",
    "    coupon = GetCouponRelatedFeature(dataset,feature)\n",
    "    \n",
    "    \n",
    "    dataset = pd.merge(coupon,merchant,on='merchant_id',how='left')\n",
    "    dataset = pd.merge(dataset,user,on='user_id',how='left')\n",
    "    dataset = pd.merge(dataset,user_merchant,on=['user_id','merchant_id'],how='left')\n",
    "    dataset = pd.merge(dataset,other_feature,on=['user_id','coupon_id','date_received'],how='left')\n",
    "    dataset.drop_duplicates(inplace=True)\n",
    " \n",
    "    dataset.user_merchant_buy_total = dataset.user_merchant_buy_total.replace(np.nan,0)\n",
    "    dataset.user_merchant_any = dataset.user_merchant_any.replace(np.nan,0)\n",
    "    dataset.user_merchant_received = dataset.user_merchant_received.replace(np.nan,0)\n",
    "    dataset['is_weekend'] = dataset.day_of_week.apply(lambda x:1 if x in (6,7) else 0)\n",
    "    weekday_dummies = pd.get_dummies(dataset.day_of_week)\n",
    "    weekday_dummies.columns = ['weekday'+str(i+1) for i in range(weekday_dummies.shape[1])]\n",
    "    dataset = pd.concat([dataset,weekday_dummies],axis=1)\n",
    "    if TrainFlag:\n",
    "        dataset['date'] = dataset['date'].fillna('null');\n",
    "        dataset['label'] = dataset.date.astype('str') + ':' +  dataset.date_received.astype('str')\n",
    "        dataset.label = dataset.label.apply(get_label)\n",
    "        dataset.drop(['merchant_id','day_of_week','date','date_received','coupon_count'],axis=1,inplace=True)\n",
    "    else:\n",
    "        dataset.drop(['merchant_id','day_of_week','coupon_count'],axis=1,inplace=True)\n",
    "    dataset = dataset.replace('null',np.nan)\n",
    "    return dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\software\\lib\\site-packages\\ipykernel_launcher.py:28: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "D:\\software\\lib\\site-packages\\ipykernel_launcher.py:35: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "D:\\software\\lib\\site-packages\\ipykernel_launcher.py:37: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "D:\\software\\lib\\site-packages\\ipykernel_launcher.py:39: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "D:\\software\\lib\\site-packages\\ipykernel_launcher.py:41: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "D:\\software\\lib\\site-packages\\ipykernel_launcher.py:43: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "D:\\software\\lib\\site-packages\\ipykernel_launcher.py:45: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "D:\\software\\lib\\site-packages\\ipykernel_launcher.py:47: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "D:\\software\\lib\\site-packages\\ipykernel_launcher.py:49: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------------ProcessDataSet1 done-------------------\n",
      "---------------ProcessDataSet2 done-------------------\n",
      "---------------ProcessDataSet3 done-------------------\n"
     ]
    }
   ],
   "source": [
    "ProcessDataSet1 = DataProcess(dataset1,feature1,True)\n",
    "ProcessDataSet1.to_csv(os.path.join(DataPath,'ProcessDataSet1.csv'),index=None)\n",
    "print('---------------ProcessDataSet1 done-------------------')\n",
    "ProcessDataSet2 = DataProcess(dataset2,feature2,True)\n",
    "ProcessDataSet2.to_csv(os.path.join(DataPath,'ProcessDataSet2.csv'),index=None)\n",
    "print('---------------ProcessDataSet2 done-------------------')\n",
    "ProcessDataSet3 = DataProcess(dataset3,feature3,False)\n",
    "ProcessDataSet3.to_csv(os.path.join(DataPath,'ProcessDataSet3.csv'),index=None)\n",
    "print('---------------ProcessDataSet3 done-------------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
